const connect = require('mysql');
const MysqlError = require('./Error');

class Connect {
  constructor(host, user, password, database) {
    this.$connect = connect.createConnection({
      host: host,
      user: user,
      password: password,
      database: database
    });
  }

  connect() {
    return new Promise(
      (resolve, reject) => {
        this.$connect.connect(
          (error) => {
            if(error) {
              reject(error);
            }
            else {
              resolve();
            }
          }
        )
      }
    )
  }

  disconnect() {
    this.$connect.end();
    this.$connect = void 0;
  }

  query(sql, sqlParams) {
    return new Promise(
      (resolve, reject) => {
        this.$connect.query(
          sql,
          sqlParams,
          (error, results) => {
            if(error) {
              reject(error);
            }
            else {
              resolve(results)
            }
          }
        )
      }
    )
  }

  async openAutoCommit() {
    let sql = `set autocommit = 1`;
    await this.query(sql);
  }

  async commit() {
    let sql = `commit`;
    await this.query(sql);
  }

  async rollback() {
    let sql = `rollback`;
    await this.query(sql);
  }

  async closeAutoCommit() {
    let sql = `set autocommit = 0`;
    await this.query(sql);
  }

  async easyQuery(sql, {selects, inserts, duplicates, updates, wheres, orders, page, size}) {
    let sqlParams = [];

    if(selects !== void 0) {
      sqlParams.push(connect.raw(this.processSelect(selects)));
    }

    if(inserts !== void 0) {
      sqlParams.push(inserts);
    }

    if(duplicates !== void 0) {
      sql += ` on duplicate key update ?`;
      sqlParams.push(duplicates);
    }

    if(updates !== void 0) {
      sqlParams.push(updates);
    }

    if(wheres !== void 0) {
      let {sql:whereSql, sqlParams:whereSqlParams} = this.processWhere(wheres);
      sql += ` where ${whereSql}`;
      sqlParams = sqlParams.concat(whereSqlParams);
    }

    if(orders !== void 0 && orders.length > 0) {
      sql += ' order by';

      for(let i = 0; i < orders.length; i++) {
        let order = orders[i];
        sql += ` ${order.column} ${order.type}`;

        if(i < orders.length - 1) {
          sql += ',';
        }
      }
    }

    if(Number.isInteger(page) && Number.isInteger(size)) {
      let total = 0;

      if(selects !== void 0) {
        sqlParams[0] = connect.raw('count(*) as total');
        let result = await this.query(sql, sqlParams);
        total = result[0].total;
        sqlParams[0] = connect.raw(this.processSelect(selects));
      }

      sql += ' limit ?, ?';
      sqlParams.push((page - 1) * size, size);
      let result = await this.query(sql, sqlParams);
      return {total, result};
    }
    else {
      let result = await this.query(sql, sqlParams);
      return {result};
    }
  }

  processSelect(selects) {
    if(selects.length === 0) {
      throw new MysqlError(MysqlError.SELECTS_PARAM_INVALID);
    }

    return selects.join(',');
  }

  processWhere(wheres) {
    let sql = '';
    let sqlParams = [];

    if(wheres.length === 0) {
      throw new MysqlError(MysqlError.WHERES_PARAM_INVALID);
    }

    for(let i = 0; i < wheres.length; i++) {
      let where = wheres[i];

      switch(where['join']) {
        case 'and':
          sql += ' and';
          break;

        case 'or':
          sql += ' or';
          break;

        case void 0:
          break;

        default:
          throw new MysqlError(MysqlError.WHERES_PARAM_INVALID);
      }

      if(where.children !== void 0) {
        let {sql: _sql, sqlParams: _sqlParams} = this.processWhere(where.children);
        sql += `(${_sql})`;
        sqlParams = sqlParams.concat(_sqlParams);
      }
      else {
        sql += ' ??';
        sqlParams.push(where.column);

        switch (where.op) {
          case '<':
          case '<=':
          case '=':
          case '>=':
          case '>':
            sql += ` ${where.op}`;
            sql += ' ?';
            sqlParams.push(where.value);
            break;

          case 'is null':
          case 'is not null':
            sql += ` ${where.op}`;
            break;

          default:
            throw new MysqlError(MysqlError.WHERES_PARAM_INVALID);
        }
      }
    }

    return {sql, sqlParams};
  }
}

module.exports = Connect;